import openpyxl
import json
from openpyxl.utils import range_boundaries

# 加载 Excel 文件
file_path = '/Users/nsm/Downloads/data.xlsx'
wb = openpyxl.load_workbook(file_path, data_only=True)
sheet = wb['Sheet1']  # 确保操作的是 Sheet1

# 辅助函数：获取单元格的值，考虑合并单元格
def get_cell_value(row, col):
    for merged_cell in sheet.merged_cells.ranges:
        min_col, min_row, max_col, max_row = range_boundaries(str(merged_cell))
        if min_col <= col <= max_col and min_row <= row <= max_row:
            return sheet.cell(row=min_row, column=min_col).value
    return sheet.cell(row=row, column=col).value

# 获取所有合并单元格的信息
merged_cells = list(sheet.merged_cells.ranges)

# 初始化数据结构
data_array = []

# 当前的 A, C, D 列值
current_A = None
current_C = 0
current_D = None
empty_dict = {}
indexD = 1
indexC = 1
count = 0
a_dict = {}
c_dict = {}
d_dict = {}
# 遍历所有行，从第2行开始（第1行是表头）
for row in range(2, sheet.max_row + 1):
    # 获取 A 到 I 列的值
    A = get_cell_value(row, 1)
    B = get_cell_value(row, 2)
    C = get_cell_value(row, 3)
    D = get_cell_value(row, 4)
    E = get_cell_value(row, 5)
    F = get_cell_value(row, 6)
    G = get_cell_value(row, 7)
    H = get_cell_value(row, 8)
    I = get_cell_value(row, 9)

  
    if not a_dict:
      a_dict = {A: A}
    
    if A not in a_dict:
      empty_dict = {}
      c_dict = {}
      a_dict = {A: A}
      indexC = 1
      indexD = 1


    if not c_dict:
      c_dict = {C:indexC}
      empty_dict = {C: {D: indexD}} 


    if C in c_dict:
      if C in empty_dict:
        
        if C!= D:
            c = empty_dict[C]
            if D in c:
                d = c[D]
                indexD = int(d)
            else:
                if D not in c : 
                    indexD = int(indexD) + 1
                    empty_dict[C] = {D:indexD}
        else : 
           indexC = int(c_dict[C])
           
    else:
      indexC += 1
      indexD = 1
      c_dict[C] = indexC
      empty_dict[C] = {D:indexD}
    

    # 初始化 key_parts 列表
    key_parts = []
    # 根据 C 和 D 的关系构建 key_parts
    if C != D:
        key_parts.extend(['-', str(indexC), '-', str(indexD)])
    else:
        key_parts.extend(['-', str(indexC)])

    # 使用 A 连接 key_parts
    key_parts.insert(0, str(A))  
    key = ''.join(key_parts)

    count += 1
    # 构建 value 字典
    value = {
        "key": count,  # 如果有 D 列，使用 D 作为子 key，否则为空
        "riskPoint": E or "",
        "hazardDescription": F or "",
        "consequence": G or "",
        "severityOfConsequence": H or "",
        "riskLevel": I or ""
    }

    # 移除空的 "key" 字段
    if not value["key"]:
        del value["key"]

    # 查找当前 key 是否已经存在
    existing_item = next((item for item in data_array if item['key'] == key), None)
    
    if existing_item:
        # 如果已经有该 key，追加 value
        existing_item['value'].append(value)
    else:
        # 如果没有该 key，创建新的 item
        data_array.append({
            "key": key,
            "value": [value]
        })

# 输出生成的数据结构为 JSON 格式
output_json = json.dumps(data_array, ensure_ascii=False, indent=2)

# 将结果保存到文件
with open('output.json', 'w', encoding='utf-8') as f:
    f.write(output_json)

print("数据生成完成，保存在 'output.json'")